﻿#_*_encoding:utf-8_*_
import os
import sys
import time
import datetime
import traceback
# import smtplib
from basic.myPublic import MySwitch
import basic.myGlobal as myGlobal
import requests
# import urllib.request
from basic.mySysCommon import MySysCommon
import configparser
import qrcode
from PIL import Image
import random
import json
import re
import subprocess





logger = myGlobal.getLogger()


class MyRequest(MySysCommon):
    u"""requests接口"""
    def __init__(self, myFileDispose, confPathName, logPathName):
        super(MyRequest, self).__init__()
        self.maxNumberTimes = 3  # 网络出错最大重试次数
        # self.tn = None
        # self.serverIP = ""
        self.weixinQunId = ""

        self.myFileDispose = myFileDispose
        self.logPathName = logPathName
        self.confPathName = confPathName

        self.__getConfig()
        pass


    def __del__(self):
        # self.tn.close()
        pass


    def conf_keywords(self):
        """Conf the wrap line key words. """
        wrap_keywords = ['union all', 'union', 'left hash join', 'hash join', 'left join', 'inner join', 'outer join', 'right join', 'where',
                         'select', 'from', 'having', 'full join', 'join', 'on', 'using', 'with', 'case']

        func_keywords = ['sum', 'min', 'max', 'abs', 'corr', 'count', 'exp', 'lag', 'lead', 'length', 'log',
                         'sqrt', 'stddev', 'stddev_pop', 'stddev_samp', 'then', 'end',
                         'cast', 'date_format', 'else', 'concat', 'concat_ws', 'split', 'date_add', 'date_sub',
                         'datediff', 'row_number', 'struct', 'substring', 'to_date', 'trim', 'trunc', 'window',
                         'year', 'month', 'minute', 'as', 'desc', 'asc', 'descending', 'ascending']
        return wrap_keywords, func_keywords


    def clean_sql(self, sql):
        """Clean the sql, remove all \n to flat the sql. """
        flat_sql = sql.replace('\n', ' ').strip()
        return flat_sql


    def find_keywords(self, sql, keywords):
        """Find all the keywords in `sql`.
        :param sql: The sql string being manipulated.
        :param keywords: A list of all the keywords being found in sql.
        :return: A dictionary with keys of sql keywords and values of two element lists with the starting and ending indexes of the keywords.
        """
        keyword_positions = []
        sql_len = len(sql)
        jude_list = [' ', '\n', '(', ')', '\t', ',']
        for n in range(sql_len):
            for kw in keywords:
                if sql[n:n + len(kw)].lower() == kw.lower():
                    pre_single = False
                    suf_single = False
                    if (n == 0) or (sql[n - 1] in jude_list):
                        pre_single = True
                    if (n == (sql_len - len(kw))) or (sql[n + len(kw)] in jude_list):
                        suf_single = True
                    single = all([pre_single, suf_single])
                    if single:
                        keyword_positions.append([sql[n:n + len(kw)], n, n + len(kw)])

        to_delete = []
        for kw1 in keyword_positions:
            for kw2 in keyword_positions:
                if (kw1[0].lower() in kw2[0].lower()) & (len(kw1[0]) < len(kw2[0])) & (kw1[1] >= kw2[1]) & (
                        kw2[2] <= kw2[2]):
                    to_delete.append(kw1)

        for n in to_delete:
            if n in keyword_positions:
                keyword_positions.remove(n)

        keyword_positions = sorted(keyword_positions, key=lambda x: x[1])
        return keyword_positions


    def line_keywords(self, sql, keyword_loc, wrap_keywords):
        """Add the \n to each key words.
        :param sql: The sql string being manipulated.
        :param keyword_loc: The location of key words.
        :param wrap_keywords: Wrap keywords.
        :return: String
        """
        offset = 0
        for i in keyword_loc:
            if i[0].lower() in wrap_keywords:
                sql = sql[:(i[1] + offset)] + '\n' + i[0] + '\n' + sql[(i[2] + offset):]
                offset += 2

        sql = sql.replace('\n\n', '\n')
        if sql[0] == '\n':
            sql = sql.replace(sql, sql[1:len(sql)])
        return sql


    def line_wrap_add(self, sql, wrap_add):
        """Add the addition wrap line key, such as ',' . """
        for wrap in wrap_add:
            sql = sql.replace(wrap, wrap + '\n')
        return sql


    def split_wrap(self, sql):
        """Split with \n, and strip the ' '. """
        sql_list = sql.split('\n')
        if sql_list[0] == '':
            del sql_list[0]
        if sql_list[-1] == '':
            del sql_list[-1]
        sql_list = list(map(lambda x: x.strip(), sql_list))
        return sql_list


    def str_mode_change(self, sql_list, mode, wrap_keywords, func_keywords):
        """Change the key words. Upper, lower or change nothing. """
        if mode.lower() == 'none':
            return sql_list
        else:
            for i, frag in enumerate(sql_list):
                if frag.lower() in wrap_keywords:
                    sql_list[i] = frag.lower() if mode.lower() == 'lower' else frag.upper()
                    continue
                func_list = []
                for func_v in func_keywords:
                    if func_v in frag.lower():
                        func_list.append(func_v)
                if len(func_list) > 0:
                    mark_frag = frag.lower() if mode.lower() == 'lower' else frag.upper()
                    func_loc = self.find_keywords(frag, func_list)
                    for loc in func_loc:
                        frag = frag[:loc[1]] + mark_frag[loc[1]:loc[2]] + frag[loc[2]:]
                    sql_list[i] = frag
            return sql_list


    def add_indent(self, sql_list, wrap_keywords):
        """Add the indent. """
        indent_space = '    '
        default_num = 1
        inner_num = 0
        count_left = 0
        count_right = 0
        for i, frag in enumerate(sql_list):
            if frag.lower() not in wrap_keywords:
                sql_list[i] = (default_num + inner_num * 2) * indent_space + frag
            if (frag.lower() in wrap_keywords) and (inner_num > 0):
                sql_list[i] = inner_num * 2 * indent_space + frag
            count_left += len(re.findall('\(', frag))
            count_right += len(re.findall('\)', frag))
            inner_num = count_left - count_right
        return sql_list


    def sql_format(self, sql, wrap_add=None, mode='none'):
        """Format the sql string.
        :param sql: The input sql
        :param wrap_add: Add some string to wrap a line, such as ',', 'and'
        :param mode: 'none', 'upper', 'lower'. key words lower(upper), no change.
        :return: Formatted sql string.
        """
        if not wrap_add:
            # wrap_add = [',']
            wrap_add = []

        wrap_keywords, func_keywords = self.conf_keywords()

        flat_sql = self.clean_sql(sql)
        wrap_keywords_loc = self.find_keywords(flat_sql, wrap_keywords)

        sql = self.line_keywords(flat_sql, wrap_keywords_loc, wrap_keywords)
        sql = self.progSpeciallineFeedStart(sql)

        sql = self.line_wrap_add(sql, wrap_add)
        sql_list = self.split_wrap(sql)
        sql_list = self.str_mode_change(sql_list, mode, wrap_keywords, func_keywords)
        sql_list = self.add_indent(sql_list, wrap_keywords)

        format_sql = '\n'.join(sql_list)

        return format_sql


    def progComments(self, sql):
        """处理注释换行"""
        sqlLen = len(sql)
        beg = 0
        while 1 == 1:
            nowNum = sql.find("--", beg)
            if nowNum == -1:
                break
            for num in range(nowNum, sqlLen):
                if self.mySysIsAlphabet(sql[num]) is True:
                    # logger.debug(sql[num])
                    list_sql = list(sql)
                    list_sql.insert(num-1, '\n')
                    sql = str(''.join(list_sql))
                    sqlLen = len(sql)
                    beg = num
                    # logger.debug(num)
                    # logger.debug(sqlLen)
                    break
                if (num == sqlLen - 1):
                    beg = num
                    break
        return sql


    def progSpeciallineFeedStartEnd(self, sql):
        """处理换行，前后换行"""
        targetStrList = ["union all", "union"]
        for targetStr in targetStrList:
            # targetStr = "union all"
            targetLen = len(targetStr)
            sqlLen = len(sql)
            beg = 0
            while 1 == 1:
                nowNum = sql.lower().find(targetStr.lower(), beg)
                if nowNum == -1:
                    break
                for num in range(nowNum+targetLen, sqlLen):
                    if self.mySysIsAlphabet(sql[num]) == True:
                        # logger.debug(sql[num])
                        list_sql = list(sql)
                        inserttmp = sql[nowNum+targetLen:num-1]
                        list_sql.insert(nowNum-1, inserttmp)
                        sql = str(''.join(list_sql))
                        sqlLen = len(sql)
                        beg = num + len(inserttmp) + targetLen
                        # logger.debug(num)
                        # logger.debug(sqlLen)
                        break
        return sql


    def progSpeciallineFeedStart(self, sql):
        """处理,前换行，后不换行"""
        targetStrList = [' and ', ' between ', 'order by', 'group by', 'when']
        for targetStr in targetStrList:
            sql = sql.replace(targetStr, targetStr.upper())
            targetLen = len(targetStr)
            sqlLen = len(sql)
            beg = 0
            while 1 == 1:
                nowNum = sql.lower().find(targetStr.lower(), beg)
                if nowNum == -1:
                    break
                for num in range(nowNum+targetLen, sqlLen):
                    # logger.debug(sql[num])
                    list_sql = list(sql)
                    inserttmp = '\n'
                    # logger.debug(inserttmp)
                    list_sql.insert(nowNum, inserttmp)
                    sql = str(''.join(list_sql))
                    sqlLen = len(sql)
                    beg = num + len(inserttmp) + targetLen
                    # logger.debug(num)
                    # logger.debug(sqlLen)
                    break
        return sql


    def checkSame(self, preparingTmp, preparingList, parametersTmp, parametersList):
        for num in range(len(preparingList)):
            pre = preparingList[num]
            if preparingTmp == pre:
                if parametersTmp == parametersList[num]:
                    return False
        return True


    def disposeFile(self):
        inputFileName = '{}/input.txt'.format(self.logPathName)
        if not os.path.exists(inputFileName):
            logger.debug('文件不存在：{}'.format(inputFileName))
            return None

        outputFileName = '{}/output.txt'.format(self.logPathName)
        self.mySysRemoveFile(outputFileName)

        readLines = self.mySysReadFileLines(inputFileName)
        # logger.debug(readLines)
        logger.debug(len(readLines))
        preparingList = []
        parametersList = []
        for num in range(len(readLines)):
            preparingLine = readLines[num]
            # if 'Preparing:' in preparingLine and '.insert' not in preparingLine and '.insertLogisInfo - ==>' not in preparingLine:
            if 'Preparing:' in preparingLine and '.insertLogisInfo - ==>' not in preparingLine:
                # logger.debug(preparingLine)

                httpnioexec = re.findall(r' \[(.*?)\] ', preparingLine)[0]
                logger.debug(httpnioexec)

                tmp = str(re.findall(r'\.(.*?) - ==>', preparingLine)[0])
                end = tmp.rfind('.')
                sqlId = tmp[end + 1:]
                logger.debug(sqlId)

                for tnum in range(num+1, len(readLines)):
                    parametersLine = readLines[tnum]
                    if ('.{} '.format(sqlId) in parametersLine) and (httpnioexec in parametersLine) and ('==> Parameters:' in parametersLine):
                        # logger.debug(parametersLine)
                        preparing = preparingLine.split('Preparing: ')[1]
                        preparingTmp = self.mySysStringCleanup(preparing)
                        parameters = parametersLine.split('Parameters: ')[1]
                        parametersTmp = self.mySysStringCleanup(parameters)
                        # logger.debug(preparingTmp)
                        # logger.debug(parametersTmp)
                        # logger.debug(preparingList)
                        # logger.debug(parametersList)
                        if self.checkSame(preparingTmp, preparingList, parametersTmp, parametersList):
                        # if (preparingTmp in preparingList and parametersTmp != parametersList[preparingList.index(preparingTmp)]) or (preparingTmp not in preparingList):
                            preparingList.append(preparingTmp)
                            parametersList.append(parametersTmp)

                            # 处理正则中的?
                            # regexpList = ['REGEXP_LIKE', 'REGEXP_SUBSTR', 'REGEXP_INSTR', 'REGEXP_REPLACE', 'REGEXP_COUNT', 'REGEXP_EXTRACT', 'REGEXP_MATCH_COUNT']
                            # for regexp in regexpList:
                            #     regexpStrList = re.findall(r'{}\((.*?)\) '.format(regexp), preparingLine, re.I)
                            #     # logger.debug(regexpStrList)
                            #     for regexpStr in regexpStrList:
                            #         if '?' in regexpStr:
                            #             preparing = preparing.replace(regexpStr, regexpStr.replace('?', '-=-'))

                            # 处理替换?
                            preparing = preparing.replace('?,', '_+_+__+_+_,').replace('? ', '_+_+__+_+_ ').replace('=?', '=_+_+__+_+_').replace('?)', '_+_+__+_+_)')
                            parametersSpl = parameters.split(', ')
                            for tmp in parametersSpl:
                                parameter = tmp.split('(')[0]
                                if "(Timestamp)" not in tmp:
                                    # preparing = preparing.replace('?', '\'{}\''.format(parameter), 1)
                                    preparing = preparing.replace('_+_+__+_+_', '\'{}\''.format(parameter), 1)
                                else:
                                    preparing = preparing.replace('_+_+__+_+_', 'Timestamp\'{}\''.format(parameter), 1)

                            # 格式
                            sql = self.sql_format(preparing)
                            sql = self.progComments(sql)
                            # sql = self.progSpeciallineFeedStartEnd(sql)
                            # sql = self.progSpeciallineFeedStart(sql)

                            # 恢复正则中的?
                            # sql = sql.replace('-=-', '?')

                            logger.debug(sql)

                            if '--' in preparing:
                                if '\n' in parametersLine:
                                    self.mySysWriteFile('-- --{}--\n{}{}\n{}\n{}\n\n\n'.format(sqlId, preparingLine, parametersLine, preparing, sql), outputFileName)
                                else:
                                    self.mySysWriteFile('-- --{}--\n{}{}\n\n{}\n{}\n\n\n'.format(sqlId, preparingLine, parametersLine, preparing, sql), outputFileName)
                            else:
                                if '\n' in parametersLine:
                                    self.mySysWriteFile('-- --{}--\n{}{}\n{}\n\n\n'.format(sqlId, preparingLine, parametersLine, sql), outputFileName)
                                else:
                                    self.mySysWriteFile('-- --{}--\n{}{}\n\n{}\n\n\n'.format(sqlId, preparingLine, parametersLine, sql), outputFileName)

                            break
                # break
            elif preparingLine[0:5] == '-----':
                self.mySysWriteFile('\n{}\n'.format(preparingLine), outputFileName)

        # 打开文件
        if os.path.exists(outputFileName):
            notepad = "D:/Tools/Notepad2/Notepad2.exe"
            if os.path.exists(notepad):
                subprocess.Popen('{} {}'.format(notepad, outputFileName))
            else:
                subprocess.Popen('notepad.exe {}'.format(outputFileName))


    def main(self):
        """主"""
        self.mySysClearHistoryLog(self.logPathName)

        # 处理SQL
        self.disposeFile()


    def __getConfig(self):
        fileName = '{}config.ini'.format(self.confPathName)

        cf = configparser.ConfigParser()
        cf.read(fileName)
        # self.weixinQunId = int(cf.get('CODE', 'weixinqunid'))
        # self.serverIP = cf.get('CODE', 'serverip')

